'''
Created on 2022/08/10

@author: zz
'''
import sys, os, re
import openpyxl


def write_excel_xlsx(path, sheet_name, value):
    index = len(value)
    workbook = openpyxl.Workbook()
    sheet = workbook.create_sheet("sheet1")
    sheet = workbook.active
    # sheet = workbook.create_sheet(sheet_name)
    sheet.title = sheet_name
    for i in range(0, index):
        for j in range(0, len(value[i])):
            sheet.cell(row=i+1, column=j+1, value=str(value[i][j]))
    workbook.save(path)
    print("创建xlsx格式表格写入数据成功!")

def addExcel(path, value, sheet):
    '''
	:param sheet:sheet的名称
    :param path:写入excel的路径
    :param value: 追加的数据
    :return:
    '''
    wb = openpyxl.load_workbook(path)
    wb.create_sheet(sheet)
    ws = wb[sheet]
    for ss in value:
        ws.append(ss)
    wb.save(path)
    print("xlsx格式新增sheet写入文件成功!")

def deleteExcel(path, sheet):
    delete_flag = False
    wb = openpyxl.load_workbook(path)
    for ws in wb.worksheets:
        #指定シートが存在していれば、変数にTrueを格納
        if ws.title == sheet:
            del wb[sheet]
            delete_flag = True
    if delete_flag:
        wb.save(path)
        print("xlsx格式删除sheet成功!")

def read_excel_xlsx(path, sheet_name):
    workbook = openpyxl.load_workbook(path)
    # sheet = wb.get_sheet_by_name(sheet_name)这种方式已经弃用，不建议使用
    sheet = workbook[sheet_name]
    for row in sheet.rows:
        for cell in row:
            print(cell.value, "\t", end="")
        print()


if __name__ == '__main__':
    retry = 0
    while True:
        input_file = input('Please input CSV file directory = ').strip()
        output_file = input('Please output xlsx file directory = ').strip()

        if not output_file.endswith(".xlsx"):
            print(f'{output_file} is not a Excel file.')
            sys.exit(1)

        # input_path = './header'
        if retry > 3:
            print(f'error exit.')
            sys.exit(1)
        elif not os.path.isfile(input_file):
            retry += 1
            print(f'{input_file} is not a file.')
            continue
        else:
            break

    file_date = [[""]*10 for i in range(2000)]
    lines = []
    file_line = re.compile(r'.*\n')
    index = 0
    columns = 0

    
    if os.path.isfile(input_file):
        sheet_name = os.path.basename(input_file).replace(".","_")
        print(sheet_name)
        with open(input_file, mode = 'r', encoding = 'utf-8', errors = 'ignore', newline = '\n') as fh_r:
            file_content = fh_r.read() + '\n'
            # linesカウント
            for m in re.finditer(file_line, file_content):
                lines.append(m.group())

        for line in lines:
            columns = 0
            # print(line)
            for date in line.replace("\n","").split("\t"):
                if date:
                    # print(date)
                    file_date[index][columns] = date
                columns += 1
            index += 1
        if os.path.isfile(output_file):
            deleteExcel(output_file, sheet_name)
            addExcel(output_file, file_date, sheet_name)
        else:
            write_excel_xlsx(output_file, sheet_name, file_date,)
    else:
        pass
    
    value3 = [["姓名", "性别", "年龄", "城市", "职业"],
          ["111", "女", "66", "石家庄", "运维工程师"],
          ["222", "男", "55", "南京", "饭店老板"],
          ["333", "女", "27", "苏州", "保安"],]

    # write_excel_xlsx(output_file, "new_sheet_name", value3)



